﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;

namespace ECGadmin.Common
{
    public class SqlDao
    {
        private SqlConnection _sharedConnection;
        public SqlConnection SharedConnection
        {
            get
            {
                if (_sharedConnection == null)
                {
                    _sharedConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["ECG.ConnectionString"].ConnectionString);
                }
                if(_sharedConnection.State == ConnectionState.Closed)
                    _sharedConnection.Open();
                return _sharedConnection;
            }
            set
            {
                _sharedConnection = value;
            }
        }
        

        // Constructors
        public SqlDao() { }
        public SqlDao(SqlConnection connection)
        {
            SharedConnection = connection;
        }

        // GetDbSqlCommand
        public SqlCommand GetDbSQLCommand(string sqlQuery)
        {
            var command = new SqlCommand();
            command.Connection = SharedConnection;
            command.CommandType = CommandType.Text;
            command.CommandText = sqlQuery;
            return command;
        }

        // GetDbConnection
        //protected static SqlConnection GetDbConnection()
        //{
        //    _conn = new SqlConnection(ConnectionString);
        //    if (_conn.State == ConnectionState.Closed)
        //        _conn.Open();
        //    return _conn;
        //}


        // GetDbSprocCommand
        public SqlCommand GetDbSprocCommand(string sprocName)
        {
            var command = new SqlCommand(sprocName);
            command.Connection = SharedConnection;
            command.CommandType = CommandType.StoredProcedure;
            return command;
        }

        // CreateNullParameter
        public SqlParameter CreateNullParameter(string name, SqlDbType paramType)
        {
            var parameter = new SqlParameter();
            parameter.SqlDbType = paramType;
            parameter.ParameterName = name;
            parameter.Value = DBNull.Value;
            parameter.Direction = ParameterDirection.Input;
            return parameter;
        }

        //Createparmeter bool
        public SqlParameter CreateParameter(string name, bool value)
        {
            //if (value == CommonBase.BoolNullValue)
            //{
            //    // If value is null then create a null parameter
            //    return CreateNullParameter(name, SqlDbType.Bit);
            //}
            var parameter = new SqlParameter();
            parameter.SqlDbType = SqlDbType.Bit;
            parameter.ParameterName = name;
            parameter.Value = value;
            parameter.Direction = ParameterDirection.Input;
            return parameter;
        }

        public SqlParameter CreateParameter(string name, object value)
        {
            var parameter = new SqlParameter(name, value);
            parameter.Direction = ParameterDirection.Input;
            return parameter;
        }

        //public  SqlParameter CreateParameter(string parameterName, SqlDbType type, object value)
        //{
        //    var param = new SqlParameter(parameterName, type);
        //    if (value == null || string.IsNullOrEmpty(value.ToString()) ||
        //        (type == SqlDbType.DateTime && ((DateTime)value) == DateTime.MinValue))
        //    {
        //        param.Value = DBNull.Value;
        //    }
        //    else
        //    {
        //        param.Value = value;
        //    }
        //    param.Direction = ParameterDirection.Input;
        //    return param;
        //}

        // CreateNullParameter - with size for nvarchars
        public SqlParameter CreateNullParameter(string name, SqlDbType paramType, int size)
        {
            var parameter = new SqlParameter();
            parameter.SqlDbType = paramType;
            parameter.ParameterName = name;
            parameter.Size = size;
            parameter.Value = DBNull.Value;
            parameter.Direction = ParameterDirection.Input;
            return parameter;
        }

        // CreateOutputParameter
        public SqlParameter CreateOutputParameter(string name, SqlDbType paramType)
        {
            var parameter = new SqlParameter();
            parameter.SqlDbType = paramType;
            parameter.ParameterName = name;
            parameter.Direction = ParameterDirection.Output;
            return parameter;
        }

        // Create parameter output type int
        //protected static SqlParameter CreateOutputParameter(string name, SqlDbType paramType,int value)
        //{
        //    SqlParameter parameter = new SqlParameter();
        //    parameter.SqlDbType = paramType;
        //    parameter.ParameterName = name;
        //    parameter.Value = value;
        //    parameter.Direction = ParameterDirection.Output;
        //    return parameter;
        //}

        // CreateOuputParameter - with size for nvarchars
        public SqlParameter CreateOutputParameter(string name, SqlDbType paramType, int size)
        {
            var parameter = new SqlParameter();
            parameter.SqlDbType = paramType;
            parameter.Size = size;
            parameter.ParameterName = name;
            parameter.Direction = ParameterDirection.Output;
            return parameter;
        }


        // CreateParameter - uniqueidentifier
        public SqlParameter CreateParameter(string name, Guid value)
        {
            if (value.Equals(CommonBase.GuidNullValue))
            {
                // If value is null then create a null parameter
                return CreateNullParameter(name, SqlDbType.UniqueIdentifier);
            }
            var parameter = new SqlParameter();
            parameter.SqlDbType = SqlDbType.UniqueIdentifier;
            parameter.ParameterName = name;
            parameter.Value = value;
            parameter.Direction = ParameterDirection.Input;
            return parameter;
        }


        // CreateParameter - int
        public SqlParameter CreateParameter(string name, int value)
        {
            if (value == CommonBase.IntNullValue)
            {
                // If value is null then create a null parameter
                return CreateNullParameter(name, SqlDbType.Int);
            }
            var parameter = new SqlParameter();
            parameter.SqlDbType = SqlDbType.Int;
            parameter.ParameterName = name;
            parameter.Value = value;
            parameter.Direction = ParameterDirection.Input;
            return parameter;
        }


        // CreateParameter - datetime
        public SqlParameter CreateParameter(string name, DateTime value)
        {
            if (value == CommonBase.DateTimeNullValue)
            {
                // If value is null then create a null parameter
                return CreateNullParameter(name, SqlDbType.DateTime);
            }
            var parameter = new SqlParameter();
            parameter.SqlDbType = SqlDbType.DateTime;
            parameter.ParameterName = name;
            parameter.Value = value;
            parameter.Direction = ParameterDirection.Input;
            return parameter;
        }


        // CreateParameter - nvarchar
        public SqlParameter CreateParameter(string name, string value)
        {
            if (value == CommonBase.StringNullValue)
            {
                // If value is null then create a null parameter
                return CreateNullParameter(name, SqlDbType.NVarChar);
            }
            var parameter = new SqlParameter();
            parameter.SqlDbType = SqlDbType.NVarChar;
            parameter.ParameterName = name;
            parameter.Value = value;
            parameter.Direction = ParameterDirection.Input;
            return parameter;
        }


        // ExecuteNonQuery
        public void ExecuteNonQuery(SqlCommand command)
        {
            try
            {
                if (command.Connection.State != ConnectionState.Open)
                {
                    command.Connection.Open();
                }
                command.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                throw new Exception("Error executing query", e);
            }
            finally
            {
                command.Connection.Close();
                //command.Connection.Dispose();
            }
        }


        // ExecuteScalar
        public Object ExecuteScalar(SqlCommand command)
        {
            try
            {
                if (command.Connection.State != ConnectionState.Open)
                {
                    command.Connection.Open();
                }
                return command.ExecuteScalar();
            }
            catch (Exception e)
            {
                throw new Exception("Error executing query", e);
            }
            finally
            {
                command.Connection.Close();
                //command.Connection.Dispose();
            }
        }


        // GetSingleValue
        public T GetSingleValue<T>(SqlCommand command)
        {
            T returnValue = default(T);
            try
            {
                if (command.Connection.State != ConnectionState.Open)
                {
                    command.Connection.Open();
                }
                SqlDataReader reader = command.ExecuteReader();
                if (reader.HasRows)
                {
                    reader.Read();
                    if (!reader.IsDBNull(0)) { returnValue = (T)reader[0]; }
                    reader.Close();
                }
            }
            catch (Exception e)
            {
                throw new Exception("Error populating data", e);
            }
            finally
            {
                command.Connection.Close();
                //command.Connection.Dispose();
            }
            return returnValue;
        }


        // GetSingleString
        public Int32 GetSingleInt32(SqlCommand command)
        {
            Int32 returnValue = default(int);
            try
            {
                if (command.Connection.State != ConnectionState.Open)
                {
                    command.Connection.Open();
                }
                SqlDataReader reader = command.ExecuteReader();
                if (reader.HasRows)
                {
                    reader.Read();
                    if (!reader.IsDBNull(0)) { returnValue = reader.GetInt32(0); }
                    reader.Close();
                }
            }
            catch (Exception e)
            {
                throw new Exception("Error populating data", e);
            }
            finally
            {
                command.Connection.Close();
                //command.Connection.Dispose();
            }
            return returnValue;
        }


        // GetSingleString
        public string GetSingleString(SqlCommand command)
        {
            string returnValue = null;
            try
            {
                if (command.Connection.State != ConnectionState.Open)
                {
                    command.Connection.Open();
                }
                SqlDataReader reader = command.ExecuteReader();
                if (reader.HasRows)
                {
                    reader.Read();
                    if (!reader.IsDBNull(0)) { returnValue = reader.GetString(0); }
                    reader.Close();
                }
            }
            catch (Exception e)
            {
                throw new Exception("Error populating data", e);
            }
            finally
            {
                command.Connection.Close();
                //command.Connection.Dispose();
            }
            return returnValue;
        }


        // GetStringList
        public List<string> GetStringList(SqlCommand command)
        {
            List<string> returnList = null;
            try
            {
                if (command.Connection.State != ConnectionState.Open)
                {
                    command.Connection.Open();
                }
                SqlDataReader reader = command.ExecuteReader();
                if (reader.HasRows)
                {
                    returnList = new List<string>();
                    while (reader.Read())
                    {
                        if (!reader.IsDBNull(0)) { returnList.Add(reader.GetString(0)); }
                    }
                    reader.Close();
                }
            }
            catch (Exception e)
            {
                throw new Exception("Error populating data", e);
            }
            finally
            {
                command.Connection.Close();
                //command.Connection.Dispose();
            }
            return returnList;
        }

        // GetSingleDTO
        public T GetSingleDTO<T>(ref SqlCommand command) where T : DTOBase
        {
            T dto = null;
            try
            {
                if (command.Connection.State != ConnectionState.Open)
                {
                    command.Connection.Open();
                }
                SqlDataReader reader = command.ExecuteReader();
                if (reader.HasRows)
                {
                    reader.Read();
                    DTOParser parser = DTOParserFactory.GetParser(typeof (T));
                    parser.PopulateOrdinals(reader);
                    dto = (T) parser.PopulateDTO(reader);
                    reader.Close();
                }
            }
            catch (Exception e)
            {
                throw new Exception("Error populating data", e);
            }
            finally
            {
                command.Connection.Close();
                //command.Connection.Dispose();
            }
            // return the DTO, it's either populated with data or null.
            return dto;
        }


        // GetDTOList
        public List<T> GetDTOList<T>(ref SqlCommand command) where T : DTOBase
        {
            var dtoList = new List<T>();
            try
            {
                if (command.Connection.State != ConnectionState.Open)
                {
                    command.Connection.Open();
                }
                SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);

                if (reader.HasRows)
                {
                    // Get a parser for this DTO type and populate
                    // the ordinals.
                    DTOParser parser = DTOParserFactory.GetParser(typeof (T));
                    parser.PopulateOrdinals(reader);
                    // Use the parser to build our list of DTOs.
                    while (reader.Read())
                    {
                        T dto = null;
                        dto = (T) parser.PopulateDTO(reader);
                        dtoList.Add(dto);
                    }
                    reader.Close();

                }
                else
                {
                    // Whenver there's no data, we return null.
                    dtoList = null;
                }
            }
            catch (Exception e)
            {
                throw new Exception("Error populating data", e);
            }
            finally
            {
                command.Connection.Close();
                //command.Connection.Dispose();
            }
            return dtoList;
        }

        public List<T> GetDTOList<T>(ref SqlCommand command, ref string outputParameterName,
                                               ref int scalarOutputValue) where T : DTOBase
        {
            var dtoList = new List<T>();
            try
            {
                SqlDataReader reader = command.ExecuteReader();
                scalarOutputValue = int.Parse((command.Parameters[outputParameterName].Value ?? 0).ToString());
                outputParameterName = null;

                if (reader.HasRows)
                {
                    // Get a parser for this DTO type and populate
                    // the ordinals.
                    DTOParser parser = DTOParserFactory.GetParser(typeof (T));
                    parser.PopulateOrdinals(reader);
                    // Use the parser to build our list of DTOs.
                    while (reader.Read())
                    {
                        T dto = null;
                        dto = (T) parser.PopulateDTO(reader);
                        dtoList.Add(dto);
                    }
                    reader.Close();
                }
                else
                {
                    // Whenver there's no data, we return null.
                    dtoList = null;
                }
            }
            catch (Exception e)
            {
                throw new Exception("Error populating data", e);
            }
            finally
            {
                command.Connection.Close();
                //command.Connection.Dispose();
            }
            return dtoList;
        }
    }
}